<?php
/* 
 * NEEDED:
 *  $for: subdivision/is
 *  $by: time/count
 *  $from: date
 *  $to: date
 */

//vertical bar
$names = array();
if($for == "is") {
    $sql = "SELECT code FROM information_systems";
}
if($for == "subdivision") {
    $sql = "SELECT code FROM subdivisions";
}
$rez = mysql_query($sql);
while($row = mysql_fetch_assoc($rez)) {
    $names[] = $row['code'];
}

//horisontal bar
$from = strtotime($from);
$to = strtotime($to);
$dates = array();
$from_c = $from;
while($from_c <= $to) {
    $dates[] = $from_c;
    //inc by 1 day
    $from_c += 86400;
}

//get predicted data------------------------------------------------------------
$predicts = array();

//get times for subdivisions
if($for == "subdivision" && $by == "time") {
    $sql = "SELECT f.date, s.`subdivision_code` , SUM(  `time` * f.received ) AS time
    FROM  `support_administration` s
    INNER JOIN forecast_quantities f ON ( s.measure_code = f.measure_code
    AND f.date >=  '".date('Y-m-d',$from)."'
    AND f.date <=  '".date('Y-m-d',$to)."' )
    GROUP BY f.date, subdivision_code";
    $rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$predicts[$row['subdivision_code']][$row['date']] = $row['time'];
    }
}

//get counts for subdivisions
if($for == "subdivision" && $by == "count") {
    $sql = "SELECT f.date, s.`subdivision_code` , SUM( f.received ) AS received
    FROM  `support_administration` s
    INNER JOIN forecast_quantities f ON ( s.measure_code = f.measure_code
    AND f.date >=  '".date('Y-m-d',$from)."'
    AND f.date <=  '".date('Y-m-d',$to)."' )
    GROUP BY f.date, subdivision_code";
    $rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$predicts[$row['subdivision_code']][$row['date']] = $row['received'];
    }
}

//get times for IS
if($for == "is" && $by == "time") {
    $sql = "SELECT f.date, SUM( f.received * s.time ) as time, i.is_code
    FROM  `support_administration` s
    INNER JOIN forecast_quantities f ON ( s.measure_code = f.measure_code
    AND f.date >=  '".date('Y-m-d',$from)."'
    AND f.date <=  '".date('Y-m-d',$to)."' )
    INNER JOIN is_subdivisions i ON ( i.subdivision_code = s.`subdivision_code` )
    GROUP BY f.date, is_code";
    $rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$predicts[$row['is_code']][$row['date']] = $row['time'];
    }
}

//get counts for IS
if($for == "is" && $by == "count") {
    $sql = "";
    /*$rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$predicts[$row['is_code']][$row['date']] = $row['received'];
    }*/
}
//------------------------------------------------------------------------------
//total historical data---------------------------------------------------------------------------
$total = array();

//get times for subdivisions
if($for == "subdivision" && $by == "time") {
    $sql = "SELECT DATE( CONCAT(  '2011-', MONTH( h.date ) ,  '-', DAY( h.date ) ) ) AS date, s.`subdivision_code` , SUM(  `time` * h.received ) AS time
    FROM  `support_administration` s
    INNER JOIN historical_quantities h ON ( s.measure_code = h.measure_code
    AND h.date <=  '".date('Y-m-d',$from)."' )
    GROUP BY date, subdivision_code";
    $rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$total[$row['subdivision_code']][$row['date']] = $row['time'];
    }
}

//get counts for subdivisions
if($for == "subdivision" && $by == "count") {
    $sql = "SELECT DATE( CONCAT(  '2011-', MONTH( h.date ) ,  '-', DAY( h.date ) ) ) AS date, s.`subdivision_code` , SUM( h.received ) AS received
    FROM  `support_administration` s
    INNER JOIN historical_quantities h ON ( s.measure_code = h.measure_code
    AND h.date <=  '".date('Y-m-d',$to)."' )
    GROUP BY date, subdivision_code";
    $rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$total[$row['subdivision_code']][$row['date']] = $row['received'];
    }
}

//get times for IS
if($for == "is" && $by == "time") {
    $sql = "SELECT DATE( CONCAT(  '2011-', MONTH( h.date ) ,  '-', DAY( h.date ) ) ) AS date, SUM( h.received * s.time ) as time, i.is_code
    FROM  `support_administration` s
    INNER JOIN historical_quantities h ON ( s.measure_code = h.measure_code
    AND h.date >=  '".date('Y-m-d',$from)."'
    AND h.date <=  '".date('Y-m-d',$to)."' )
    INNER JOIN is_subdivisions i ON ( i.subdivision_code = s.`subdivision_code` )
    GROUP BY date, is_code";
    $rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$total[$row['is_code']][$row['date']] = $row['time'];
    }
}

//get counts for IS
if($for == "is" && $by == "count") {
    $sql = "";
    /*$rez = mysql_query($sql);
    while($row = mysql_fetch_assoc($rez)) {
	$predicts[$row['is_code']][$row['date']] = $row['received'];
    }*/
}
//------------------------------------------------------------------------------
//avarage historical data-------------------------------------------------------
//$avarage = array();
//key: object name(is, subdivision); value: array($time=>value)
//foreach($total as $key => $value) {
//    print "</br>".$key.":";
//    print_r($value);
//}
//------------------------------------------------------------------------------
?>

<?php
//output document
if($for == "is") {
    print "IS apkrovimas";
    if($by == "count") {
	print "</br><label style='color:red'>Šiuo metu neveikia.</label>";
    }
}
if($for == "subdivision") {
    print "Padalinių apkrovimas";
}

//draw table
print "<table border=1>";
print "<tr><th></th>";
foreach($dates as $date) {
    print "<th>".date("Y-m-d", $date)."</th>";
}
print "</tr>";
foreach($names as $name) {
    print "<tr>";
    print "<td>";
    print $name;
    print "</td>";
    foreach($dates as $date) {
	print "<td>";
	if(isset($predicts[$name][date("Y-m-d", $date)])) {
	    print "<label id='predicted' style='color:red;'>+".$predicts[$name][date("Y-m-d", $date)]."</label><br/>";
	} else {
	    print "<label id='predicted'>0</label><br/>";
	}
	if(isset($total[$name][date("Y-m-d", $date)])) {
	    print "<label id='predicted' style='color:blue;'>".$total[$name][date("Y-m-d", $date)]."</label>";
	} else {
	    print "<label id='predicted'>0</label>";
	}
	print "</td>";
    }
    print "</tr>";
}
print "</table>";
?>
Raudona: importuoti duomenys - numatytos paraiškos apimtys<br/>
Melynas: isviso gautų paraiškų suma iki ieskomo laikotarpio